import sys
import os
import time
from pysqlite2 import dbapi2 as sqlite


connection = sqlite.connect('./database/keys.db')
cursor = connection.cursor()


def setup_table():
	cursor.execute('CREATE TABLE session (id INTEGER PRIMARY KEY, key VARCHAR(30), address VARCHAR(15), date VARCHAR(20))')
	connection.commit()


def write_value(key, address, date = None):
	if not date: date = time.time()

	cursor.execute('INSERT INTO session VALUES (null, ?, ?, ?)', (key, address, date))
	connection.commit()


def read_values():
	cursor.execute('SELECT * FROM session')
	for row in cursor:
		print 'id: %i, key: %s, address: %s, date: %s' % (row[0], row[1], row[2], row[3])


def read_unique_keys():
	keys = []
	rows = []

	cursor.execute('SELECT * FROM session')
	for row in cursor:
		if not row[1] in keys:
			rows.append(row)
			keys.append(row[1])

	n = 0
	for row in rows:
		n += 1
		print '#%i] id: %i, key: %s, address: %s, date: %s' % (n, row[0], row[1], row[2], row[3])


def read_lulz():
	rows = []

	cursor.execute('SELECT * FROM session')
	for row in cursor:
		if row[0] in [100, 200, 300, 400, 500, 600, 700, 800, 900, 1000]:
			rows.append(row)

	n = 0
	for row in rows:
		n += 1
		print '#%i] id: %i, key: %s, address: %s, date: %s' % (n, row[0], row[1], row[2], row[3])


if __name__ == '__main__':
	if sys.argv[1] == 'init':
		setup_table()

	elif sys.argv[1] == 'insert':
		write_value(sys.argv[2].replace('~', ' '), sys.argv[3])

	elif sys.argv[1] == 'read':
		read_values()

	elif sys.argv[1] == 'unique-keys':
		read_unique_keys()

	elif sys.argv[1] == 'lul':
		read_lulz()


'''
if __name__ == '__main__':
	while 1:
		print '?: Quit  1: Create Table  2: Write Value  3: Read Values'
		i = raw_input('> ')
		if i == '?':
			break
		elif i == '1':
			setup_table()
			print 'Table created.'
			print ''
		elif i == '2':
			key = raw_input('Key: ')
			address = raw_input('Address: ')
			write_value(key, address)
			print 'Row inserted.'
			print ''
		elif i == '3':
			read_values()
			print ''
'''